from flask import Flask, render_template
from flask import request
from flask.json import jsonify
from flask_cors import CORS
from db import DB,config
import pandas as pd
import datetime
import numpy as np

#构造当前函数的应用app（__name__为当前函数名）
app=Flask(__name__)
CORS(app)
#app的路由(url为与装饰函数绑定的url地址，options为转发给url对象的参数列表)
@app.route('/flowSearch',methods=['GET','POST'])
def flowSearch(): #app.route装饰的为视图函数，用于处理数据和业务逻辑，返回响应内容（展示结果）应该为模板的作用
    if request.method=='POST':
        tmp=request.form
        content=tmp['content']
        start_time=tmp['start_time']
        end_time = tmp['end_time']
        startDate=pd.to_datetime(start_time, format='%Y-%m-%d')
        endDate=pd.to_datetime(end_time, format='%Y-%m-%d')
        db = DB(config)
        if content in ['1','2','4']:
            sql1 = 'select daylinetotalinflow.' + content + " from daylinetotalinflow where date>='" +startDate.strftime('%Y-%m-%d')+"' and date<='"+endDate.strftime('%Y-%m-%d')+"';"
            res1 = [x[content] for x in db.select(sql1)]

            sql2 = 'select daylinetotaloutflow.' + content + " from daylinetotaloutflow where date>='" +startDate.strftime('%Y-%m-%d')+"' and date<='"+endDate.strftime('%Y-%m-%d')+"';"
            res2 = [x[content] for x in db.select(sql2)]

            sql3 = 'select hourlinetotalinflow3.' + content + " from hourlinetotalinflow3 where dateTime>='" + start_time+' 00:00:00' + "' and dateTime<='" + end_time +' 23:50:00'+"';"
            res3 = [x[content] for x in db.select(sql3)]
            len3=len(res3)
            print(len3)
            res3= np.array(res3).reshape((len3//24,24))
            res3=[res3[:,x].sum() for x in range(24)]
            res3 = [int(x) for x in res3]

            sql4 = 'select hourlinetotaloutflow3.' + content + " from hourlinetotaloutflow3 where dateTime>='" + start_time + ' 00:00:00' + "' and dateTime<='" + end_time + ' 23:50:00' + "';"
            res4 = [x[content] for x in db.select(sql4)]
            len4=len(res4)
            print(len4)
            res4= np.array(res4).reshape((len4//24,24))
            res4=[res4[:,x].sum() for x in range(24)]
            res4 = [int(x) for x in res4]

            sql5 = 'select minutelinetotalinflow3.' + content + " from minutelinetotalinflow3 where dateTime>='" + start_time + ' 00:00:00' + "' and dateTime<='" + end_time + ' 23:50:00' + "';"
            res5 = [x[content] for x in db.select(sql5)]
            len5=len(res5)
            print(len5)
            res5= np.array(res5).reshape((len5//144,144))
            res5=[res5[:,x].sum() for x in range(144)]
            res5 = [int(x) for x in res5]

            sql6 = 'select minutelinetotaloutflow3.' + content + " from minutelinetotaloutflow3 where dateTime>='" + start_time + ' 00:00:00' + "' and dateTime<='" + end_time + ' 23:50:00' + "';"
            res6 = [x[content] for x in db.select(sql6)]
            len6=len(res6)
            print(len6)
            res6= np.array(res6).reshape((len6//144,144))
            res6=[res6[:,x].sum() for x in range(144)]
            res6 = [int(x) for x in res6]
        else:
            oneDay = datetime.timedelta(days=1)
            startYesterDay = pd.to_datetime(startDate - oneDay, format='%Y-%m-%d')
            sql1 = 'select ' + content + " from daystationtotalinflow where date>='"+startDate.strftime('%Y-%m-%d')+"' and date<='"+endDate.strftime('%Y-%m-%d')+"';"
            res1=[x[content] for x in db.select(sql1)]

            sql2 = 'select ' + content + " from daystationtotaloutflow where date>='"+startDate.strftime('%Y-%m-%d')+"' and date<='"+endDate.strftime('%Y-%m-%d')+"';"
            res2=[x[content] for x in db.select(sql2)]

            sql3 = 'select hourstationtotalinflow3.' + content + " from hourstationtotalinflow3 where dateTime>='" + start_time+' 00:00:00' + "' and dateTime<='" + end_time +' 23:50:00'+"';"
            res3 = [x[content] for x in db.select(sql3)]
            len3=len(res3)
            print(len3)
            res3= np.array(res3).reshape((len3//24,24))
            res3=[res3[:,x].sum() for x in range(24)]
            res3 = [int(x) for x in res3]

            sql4 = 'select hourstationtotaloutflow3.' + content + " from hourstationtotaloutflow3 where dateTime>='" + start_time + ' 00:00:00' + "' and dateTime<='" + end_time + ' 23:50:00' + "';"
            res4 = [x[content] for x in db.select(sql4)]
            len4=len(res4)
            print(len4)
            res4= np.array(res4).reshape((len4//24,24))
            res4=[res4[:,x].sum() for x in range(24)]
            res4 = [int(x) for x in res4]

            sql5 = 'select minutestationtotalinflow3.' + content + " from minutestationtotalinflow3 where dateTime>='" + start_time + ' 00:00:00' + "' and dateTime<='" + end_time + ' 23:50:00' + "';"
            res5 = [x[content] for x in db.select(sql5)]
            len5=len(res5)
            print(len5)
            res5= np.array(res5).reshape((len5//144,144))
            res5=[res5[:,x].sum() for x in range(144)]
            res5 = [int(x) for x in res5]

            sql6 = 'select minutestationtotaloutflow3.' + content + " from minutestationtotaloutflow3 where dateTime>='" + start_time + ' 00:00:00' + "' and dateTime<='" + end_time + ' 23:50:00' + "';"
            res6 = [x[content] for x in db.select(sql6)]
            len6=len(res6)
            print(len6)
            res6= np.array(res6).reshape((len6//144,144))
            res6=[res6[:,x].sum() for x in range(144)]
            res6=[int(x) for x in res6]

        print([((str(x) if x>=10 else '0'+str(x))+':'+str(y)+'0:00' ) for x in range(24) for y in range(6)])
        print(res5)
        dateTime2=[((str(x) if x >= 10 else '0' + str(x)) + ':' + str(y) + '0:00') for x in range(24) for y in range(6)]
        dataTmp=pd.DataFrame()
        dataTmp['dateTime']=dateTime2
        dataTmp=dataTmp.set_index(['dateTime'])
        dataTmp['In']=res5
        dataTmp['Out'] = res6
        amTopIn=list(dataTmp.loc[dateTime2[:72]].sort_values(['In'],ascending=False).index)[0]
        pmTopIn = list(dataTmp.loc[dateTime2[72:]].sort_values(['In'], ascending=False).index)[0]
        amTopOut = list(dataTmp.loc[dateTime2[:72]].sort_values(['Out'], ascending=False).index)[0]
        pmTopOut =list(dataTmp.loc[dateTime2[72:]].sort_values(['Out'],ascending=False).index)[0]
        return jsonify({'isLine':(True if content in ['1','2','4'] else False),'inTotal':sum(res3),'outTotal':sum(res4),'amTopIn':amTopIn,'pmTopIn':pmTopIn,'amTopOut':amTopOut,'pmTopOut':pmTopOut,'date':[x.strftime('%Y-%m-%d') for x in pd.date_range(start=start_time,end=end_time).tolist()],'dateTime1':[(str(x) if x>=10 else '0'+str(x))+':00:00' for x in range(24)],'dateTime2':dateTime2,'title':(content+'号线' if content in ['1','2','4'] else content),'dayIn': res1, 'dayOut': res2,'hourIn':res3,'hourOut':res4,'minuteIn':res5,'minuteOut':res6})
    else:
        return 'error'

@app.route('/flowPredict',methods=['GET','POST'])
def flowPredict(): #app.route装饰的为视图函数，用于处理数据和业务逻辑，返回响应内容（展示结果）应该为模板的作用
    if request.method=='POST':
        tmp=request.form
        content=tmp['content']
        start_time='2019-01-26'
        end_time = '2019-01-26'
        startDate=pd.to_datetime(start_time, format='%Y-%m-%d')
        endDate=pd.to_datetime(end_time, format='%Y-%m-%d')
        db = DB(config)
        if content in ['1','2','4']:
            sql3 = 'select predicthourlinetotalinflow.' + content + " from predicthourlinetotalinflow where dateTime>='" + start_time+' 00:00:00' + "' and dateTime<='" + end_time +' 23:50:00'+"';"
            res3 = [x[content] for x in db.select(sql3)]
            len3=len(res3)
            print(len3)
            res3= np.array(res3).reshape((len3//24,24))
            res3=[res3[:,x].sum() for x in range(24)]
            res3 = [int(x) for x in res3]

            sql4 = 'select predicthourlinetotaloutflow.' + content + " from predicthourlinetotaloutflow where dateTime>='" + start_time + ' 00:00:00' + "' and dateTime<='" + end_time + ' 23:50:00' + "';"
            res4 = [x[content] for x in db.select(sql4)]
            len4=len(res4)
            print(len4)
            res4= np.array(res4).reshape((len4//24,24))
            res4=[res4[:,x].sum() for x in range(24)]
            res4 = [int(x) for x in res4]

            sql5 = 'select predictminutelinetotalinflow.' + content + " from predictminutelinetotalinflow where dateTime>='" + start_time + ' 00:00:00' + "' and dateTime<='" + end_time + ' 23:50:00' + "';"
            res5 = [x[content] for x in db.select(sql5)]
            len5=len(res5)
            print(len5)
            res5= np.array(res5).reshape((len5//144,144))
            res5=[res5[:,x].sum() for x in range(144)]
            res5 = [int(x) for x in res5]

            sql6 = 'select predictminutelinetotaloutflow.' + content + " from predictminutelinetotaloutflow where dateTime>='" + start_time + ' 00:00:00' + "' and dateTime<='" + end_time + ' 23:50:00' + "';"
            res6 = [x[content] for x in db.select(sql6)]
            len6=len(res6)
            print(len6)
            res6= np.array(res6).reshape((len6//144,144))
            res6=[res6[:,x].sum() for x in range(144)]
            res6 = [int(x) for x in res6]
        else:
            sql3 = 'select predicthourstationtotalinflow.' + content + " from predicthourstationtotalinflow where dateTime>='" + start_time+' 00:00:00' + "' and dateTime<='" + end_time +' 23:50:00'+"';"
            res3 = [x[content] for x in db.select(sql3)]
            len3=len(res3)
            print(len3)
            res3= np.array(res3).reshape((len3//24,24))
            res3=[res3[:,x].sum() for x in range(24)]
            res3 = [int(x) for x in res3]

            sql4 = 'select predicthourstationtotaloutflow.' + content + " from predicthourstationtotaloutflow where dateTime>='" + start_time + ' 00:00:00' + "' and dateTime<='" + end_time + ' 23:50:00' + "';"
            res4 = [x[content] for x in db.select(sql4)]
            len4=len(res4)
            print(len4)
            res4= np.array(res4).reshape((len4//24,24))
            res4=[res4[:,x].sum() for x in range(24)]
            res4 = [int(x) for x in res4]

            sql5 = 'select predictminutestationtotalinflow.' + content + " from predictminutestationtotalinflow where dateTime>='" + start_time + ' 00:00:00' + "' and dateTime<='" + end_time + ' 23:50:00' + "';"
            res5 = [x[content] for x in db.select(sql5)]
            len5=len(res5)
            print(len5)
            res5= np.array(res5).reshape((len5//144,144))
            res5=[res5[:,x].sum() for x in range(144)]
            res5 = [int(x) for x in res5]

            sql6 = 'select predictminutestationtotaloutflow.' + content + " from predictminutestationtotaloutflow where dateTime>='" + start_time + ' 00:00:00' + "' and dateTime<='" + end_time + ' 23:50:00' + "';"
            res6 = [x[content] for x in db.select(sql6)]
            len6=len(res6)
            print(len6)
            res6= np.array(res6).reshape((len6//144,144))
            res6=[res6[:,x].sum() for x in range(144)]
            res6=[int(x) for x in res6]

        dateTime2=[((str(x) if x >= 10 else '0' + str(x)) + ':' + str(y) + '0:00') for x in range(24) for y in range(6)]
        dataTmp=pd.DataFrame()
        dataTmp['dateTime']=dateTime2
        dataTmp=dataTmp.set_index(['dateTime'])
        dataTmp['In']=res5
        dataTmp['Out'] = res6
        amTopIn=list(dataTmp.loc[dateTime2[:72]].sort_values(['In'],ascending=False).index)[0]
        pmTopIn = list(dataTmp.loc[dateTime2[72:]].sort_values(['In'], ascending=False).index)[0]
        amTopOut = list(dataTmp.loc[dateTime2[:72]].sort_values(['Out'], ascending=False).index)[0]
        pmTopOut =list(dataTmp.loc[dateTime2[72:]].sort_values(['Out'],ascending=False).index)[0]
        return jsonify({'isLine':(True if content in ['1','2','4'] else False),'inTotal':sum(res3),'outTotal':sum(res4),'amTopIn':amTopIn,'pmTopIn':pmTopIn,'amTopOut':amTopOut,'pmTopOut':pmTopOut,'dateTime1':[(str(x) if x>=10 else '0'+str(x))+':00:00' for x in range(24)],'dateTime2':dateTime2,'title':(content+'号线' if content in ['1','2','4'] else content),'hourIn':res3,'hourOut':res4,'minuteIn':res5,'minuteOut':res6})
    else:
        return 'error'

@app.route('/index',methods=['GET','POST'])
def index():
    return render_template('index.html')

@app.route('/FlowContrl',methods=['GET','POST'])
def FlowContrl():
    return render_template('FlowContrl.html')

@app.route('/searchCount',methods=['GET','POST'])
def searchCount():
    return render_template('searchCount.html')

@app.route('/predict',methods=['GET','POST'])
def predict():
    return render_template('predict.html')

#运行服务起应用(host设为0.0.0.0可外部访问，port一般5000，debug默认false，optinos为要转发到底层的Werkzeug服务器。)
app.run()